#!/usr/bin/python
# -*- coding: utf-8 -*-
#
# File: afla_flokkun.py
# Author: Finnur Smári Torfason
# Date: 25/07/2011  
# Flokkun á gögnum í aflalista

import sqlite3, xlwt

def main(date1, date2, filename):
  wb = xlwt.Workbook('utf-8')
	
  date1 = parseDate(date1)
  date2 = parseDate(date2)
	
  # Vel allar landanir eftir dagsetningu
  boat,landing = fetch(date1, date2)
	
  total = calcTotal(boat,landing)
  splitTotal = splitList(total)
	
  saveExcel(splitTotal[0], wb, filename, 'Smábátar 10+')
  saveExcel(splitTotal[1], wb, filename, 'Smábátar 10-')
  saveExcel(splitTotal[2], wb, filename, 'Net')
  saveExcel(splitTotal[3], wb, filename, 'Humar')
  saveExcel(splitTotal[4], wb, filename, 'Dragnót')
  saveExcel(splitTotal[5], wb, filename, 'Lína')
  saveExcel(splitTotal[6], wb, filename, 'Rækja')
  saveExcel(splitTotal[7], wb, filename, 'Troll')
  saveExcel(splitTotal[8], wb, filename, 'Frystitogarar')
  saveExcel(splitTotal[9], wb, filename, 'Uppsjávarveiði')
  saveExcel(splitTotal[10], wb, filename, 'Annað')

def calcTotal(boat,landing):
	total = []
	
	# Reikna heildarafla eftir veiðarfærum
	for i in range(0,len(boat)):
		tmp = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
		tmpCtr = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
		tmpMax = [0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
		tmpHarb = ['', '', '', '', '', '', '', '', '', '']
		
		for j in range(0,len(landing)):
			if landing[j][0] == boat[i][0]:
				
				if ('krókaaflamarksbátur' in boat[i][3].encode('utf-8').lower()) or ('smábátur' in boat[i][3].encode('utf-8').lower()) or ('strandveiðar' in boat[i][3].encode('utf-8').lower()):
					if float(boat[i][2]) < 10.0:
						tmp[0] += float(landing[j][5])
						if landing[j][4] not in tmpHarb[0]:
							tmpHarb[0] = tmpHarb[0] + landing[j][4] + ', '
						if 'Gámur' not in landing[j][4].encode('utf-8'):
							tmpCtr[0] += 1
						if float(landing[j][5]) > tmpMax[0]:
							tmpMax[0] = float(landing[j][5])
					else:
						tmp[1] += float(landing[j][5])
						if landing[j][4] not in tmpHarb[1]:
							tmpHarb[1] = tmpHarb[1] + landing[j][4] + ', '
						if 'Gámur' not in landing[j][4].encode('utf-8'):
							tmpCtr[1] += 1
						if float(landing[j][5]) > tmpMax[1]:
							tmpMax[1] = float(landing[j][5])
				
				elif 'net' in landing[j][3].encode('utf-8').lower():
					tmp[2] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[2]:
						tmpHarb[2] = tmpHarb[2] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[2] += 1
					if float(landing[j][5]) > tmpMax[2]:
						tmpMax[2] = float(landing[j][5])
				
				elif 'Humarvarpa' in landing[j][3].encode('utf-8'):
					tmp[3] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[3]:
						tmpHarb[3] = tmpHarb[3] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[3] += 1
					if float(landing[j][5]) > tmpMax[3]:
						tmpMax[3] = float(landing[j][5])
				
				elif 'Dragnót' in landing[j][3].encode('utf-8'):
					tmp[4] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[4]:
						tmpHarb[4] = tmpHarb[4] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[4] += 1
					if float(landing[j][5]) > tmpMax[4]:
						tmpMax[4] = float(landing[j][5])
				
				elif 'Lína' in landing[j][3].encode('utf-8'):
					tmp[5] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[5]:
						tmpHarb[5] = tmpHarb[5] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[5] += 1
					if float(landing[j][5]) > tmpMax[5]:
						tmpMax[5] = float(landing[j][5])
				
				elif 'Rækjuvarpa' in landing[j][3].encode('utf-8'):
					tmp[6] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[6]:
						tmpHarb[6] = tmpHarb[6] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[6] += 1
					if float(landing[j][5]) > tmpMax[6]:
						tmpMax[6] = float(landing[j][5])
				
				elif 'Botnvarpa' in landing[j][3].encode('utf-8'):
					tmp[7] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[7]:
						tmpHarb[7] = tmpHarb[7] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[7] += 1
					if float(landing[j][5]) > tmpMax[7]:
						tmpMax[7] = float(landing[j][5])
				
				elif 'flotvarpa' in landing[j][3].encode('utf-8').lower():
					tmp[8] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[8]:
						tmpHarb[8] = tmpHarb[8] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[8] += 1
					if float(landing[j][5]) > tmpMax[8]:
						tmpMax[8] = float(landing[j][5])
				
				elif 'nót' in landing[j][3].encode('utf-8').lower():
					tmp[8] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[8]:
						tmpHarb[8] = tmpHarb[8] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[8] += 1
					if float(landing[j][5]) > tmpMax[8]:
						tmpMax[8] = float(landing[j][5])
				
				else:
					tmp[9] += float(landing[j][5])
					if landing[j][4] not in tmpHarb[9]:
						tmpHarb[9] = tmpHarb[9] + landing[j][4] + ', '
					if 'Gámur' not in landing[j][4].encode('utf-8'):
						tmpCtr[9] += 1
					if float(landing[j][5]) > tmpMax[9]:
						tmpMax[9] = float(landing[j][5])
		if tmp.count(0) != 10:
			total.append((boat[i][0], getName(boat, boat[i][0]), tmpCtr, tmp, tmpMax, tmpHarb))

	return total

def saveExcel(aList, wb, filename, name):

	bList = sorted(aList, key=lambda tot: tot[3], reverse=True)
	aList = removeDuplicates(bList)

	style0 = xlwt.easyxf('font: name Arial, color-index black', num_format_str='#,##0')
	style1 = xlwt.easyxf('font: name Arial, color-index black, bold on', num_format_str='#,##0')
	
	ws = wb.add_sheet(name)

	ws.write(0,0,'Skipaskrárnúmer', style1)
	ws.write(0,1,'Nafn', style1)
	ws.write(0,2,'Fjöldi', style1)
	ws.write(0,3,'Heildarafli', style1)
	ws.write(0,4,'Mest', style1)
	ws.write(0,5,'Löndunarhöfn', style1)
	
	for i in range(0, len(aList)):
		try:
			boatName = aList[i][1].encode('utf-8')
		except UnicodeDecodeError:
			boatName = aList[i][1]
		try:
			harb = aList[i][5].encode('utf-8')
		except UnicodeDecodeError:
			harb = aList[i][5]
			
		ws.write(i+1, 0, str(aList[i][0]), style0)
		ws.write(i+1, 1, boatName, style0)
		ws.write(i+1, 2, str(aList[i][2]), style0)
		ws.write(i+1, 3, str(aList[i][3]/1000), style0)
		ws.write(i+1, 4, str(aList[i][4]/1000), style0)
		ws.write(i+1, 5, harb, style0)

	wb.save(filename)

def removeDuplicates(bList):
	aList = []
	for i in bList:
		if i not in aList:
				aList.append(i)
	
	return aList

def fetch(date1, date2):

	# Tengist gagnagrunni
	con = sqlite3.connect('./db/aflalisti.sql')
	cur = con.cursor()

	# Sæki landanir
	cur.execute("SELECT * FROM landing WHERE date BETWEEN date('" + str(date1) +  "') AND date('" + str(date2) + "')")
	landing = cur.fetchall()

	# Sæki báta
	cur.execute("SELECT * FROM boat")
	boat = cur.fetchall()

	# Loka gagnagrunni
	cur.close()
	con.close()

	return (boat, landing)

def parseDate(date):

	month = str(date.Month + 1)
	day = str(date.Day)

	if len(month) < 2:
		month = '0' + month
	if len(day) < 2:
		day = '0' + day
	
	return str(date.Year) + '-' + month + '-' + day

def getName(boat, num):
	name = ''
	for i in range(0, len(boat)):
		if boat[i][0] == num:
			name = boat[i][1]
			break
	
	return name

def splitList(aList):
	togarar = [1270, 1308, 1345, 1351, 1360, 1530, 1579, 1628, 1833, 1868, 1902, 1937, 1972, 1976, 1977, 2067, 2170, 2182, 2184, 2197, 2203, 2265, 2549, 2626, 2750, 2770]

	frysti = []
	plus10 = []
	minus10 = []
	net = []
	humar = []
	dragnot = []
	lina = []
	raekja = []
	troll = []
	upp = []
	annad = []

	for i in range(0, len(aList)):
		if aList[i][2][0] != 0: 
			minus10.append((aList[i][0], aList[i][1], aList[i][2][0], aList[i][3][0], aList[i][4][0], aList[i][5][0]))
		if aList[i][2][1] != 0: 
			plus10.append((aList[i][0], aList[i][1], aList[i][2][1], aList[i][3][1], aList[i][4][1], aList[i][5][1]))	
		if aList[i][2][2] != 0: 
			net.append((aList[i][0], aList[i][1], aList[i][2][2], aList[i][3][2], aList[i][4][2], aList[i][5][2]))
		if aList[i][2][3] != 0: 
			humar.append((aList[i][0], aList[i][1], aList[i][2][3], aList[i][3][3], aList[i][4][3], aList[i][5][3]))
		if aList[i][2][4] != 0: 
			dragnot.append((aList[i][0], aList[i][1], aList[i][2][4], aList[i][3][4], aList[i][4][4], aList[i][5][4]))
		if aList[i][2][5] != 0: 
			lina.append((aList[i][0], aList[i][1], aList[i][2][5], aList[i][3][5], aList[i][4][5], aList[i][5][5]))
		if aList[i][2][6] != 0: 
			raekja.append((aList[i][0], aList[i][1], aList[i][2][6], aList[i][3][6], aList[i][4][6], aList[i][5][6]))
		if (aList[i][0] in togarar) and (aList[i][2][7] != 0):
			frysti.append((aList[i][0], aList[i][1], aList[i][2][7], aList[i][3][7], aList[i][4][7], aList[i][5][7]))
		if (aList[i][2][7] != 0) and (aList[i][0] not in togarar): 
			troll.append((aList[i][0], aList[i][1], aList[i][2][7], aList[i][3][7], aList[i][4][7], aList[i][5][7]))
		if aList[i][2][8] != 0: 
			upp.append((aList[i][0], aList[i][1], aList[i][2][8], aList[i][3][8], aList[i][4][8], aList[i][5][8]))
		if aList[i][2][9] != 0: 
			annad.append((aList[i][0], aList[i][1], aList[i][2][9], aList[i][3][9], aList[i][4][9], aList[i][5][9]))

	return (plus10, minus10, net, humar, dragnot, lina, raekja, troll, frysti, upp, annad)
